Skip to main content

On Completed Agreement Update Document Approvals

Flow Overview​

  • Name: On Completed Agreement Update Document Approvals
  • Purpose: Check the status of all outstanding agreements within Adobe Sign
  • Date Created / Last Modified: Useful for tracking changes.

Dependencies​

  • Dataverse
    • Document Approval
    • Contact Document
    • Document Location
  • Adobe Sign

Trigger​

  • Type: Automated
  • Connector: Adobe Sign
  • Action Type: When an agreement workflow is completed successfully
  • Parameters:
    • Include Agreement Info?: Yes
    • Include Agreement Participant Info?: Yes
    • Include Agreement Document Info?: Yes
    • Include Agreement Signed Document Info?: Yes

Get a PDF of a signed agreement​

  • Connector: Adobe Sign
  • Action Type: Get a PDF of a signed agreement
    • Agreement ID: @{triggerOutputs()?['body/agreement/id']}

List Document Approvals with Agreement ID​

  • Connector: Dataverse
  • Action Type: List Rows
    • Table Name: Document Approvals
    • Fetxh XML Query: 
      <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="tt_documentapproval">
          <attribute name="tt_name" />
          <attribute name="statuscode" />
          <attribute name="tt_contactid" />
          <attribute name="tt_documentapprovalid" />
          <order attribute="tt_name" descending="false" />
          <filter type="and">
          <condition attribute="tt_agreementids" operator="like" value="%@{triggerOutputs()?['body/agreement/id']}%" />
          </filter>
      </entity>
      </fetch>

Initialize Folder Path​

  • Action Type: Initialise Variable
    • Name: Folder Path
    • Type: String
    • Value:

Initialize Status Reason​

  • Action Type: Initialise Variable
    • Name: Status Reason
    • Type: Integer
    • Value:

Initialize Status​

  • Action Type: Initialise Variable
    • Name: Status
    • Type: Integer
    • Value:

Initialize Agreement Url​

  • Action Type: Initialise Variable
    • Name: Agreement Url
    • Type: Array
    • Value: []

Initialize AllFilesSigned​

  • Action Type: Initialise Variable
    • Name: AllFilesSigned
    • Type: Boolean
    • Value: @{true}

Initialize File Name​

  • Action Type: Initialise Variable
    • Name: File Name
    • Type: String
    • Value:

For Each Document Approval​

  • Action Type: Apply to Each
    • Value: @{outputs('List_Document_Approvals_with_Agreement_ID')?['body/value']}

Get Document Approval by ID​

  • Connector: Dataverse
  • Action Type: Get a Row by ID
    • Table Name: Document Approvals
    • Row ID: @{items('For_Each_Document_Approval')?['tt_documentapprovalid']}

Set Variable​

  • Action Type: Set Variable
    • Name: Agreement Url
    • Value: []

Compose Agreement Url Array​

  • Action Type: Compose
    • Value: @{outputs('Get_Document_Approval_by_ID')?['body/tt_agreementitems']}

Parse JSON​

  • Action Type: Parse JSON
    • Content: @{outputs('Compose_Agreement_Url_Array')}
    • Schema: 
      {
          "type": "array",
          "items": {
              "type": "object",
              "properties": {
                  "DocumentName": {
                      "type": "string"
                  },
                  "DocumentURL": {
                      "type": "string"
                  },
                  "Status": {
                      "type": "string"
                  },
                  "AgreementID": {
                      "type": "string"
                  }
              },
              "required": [
                  "DocumentName",
                  "DocumentURL",
                  "Status",
                  "AgreementID"
              ]
          }
      }

Apply to each Agreement Url​

  • Action Type: Apply to Each
    • Value: @{body('Parse_JSON')}

Agreement is Signed​

  • Action Type: Condition
    • Condition Type: AND
      • Condition: @{items('Apply_to_each_Agreement_Url')['AgreementID']} is equal to @{triggerOutputs()?['body/agreement/id']}
Compose Signed Item​
  • Action Type: Compose
  • Inputs: 
    {
        "DocumentName": "@{items('Apply_to_each_Agreement_Url')['DocumentName']}",
        "DocumentURL": "@{items('Apply_to_each_Agreement_Url')['DocumentURL']}",
        "Status": "Signed",
        "AgreementID": "@{items('Apply_to_each_Agreement_Url')['AgreementID']}"
    }
Set File Name​
  • Action Type: Set Variable
    • Name: Agreement Url
    • Value: @{items('Apply_to_each_Agreement_Url')['DocumentName']}
Append Signed Item to Agreement Url​
  • Action Type: Append to array variable
    • Name: Agreement Url
    • Value: @{outputs('Compose_Signed_Item')}

Compose Agreement Url String​

  • Action Type: Compose
    • Inputs: @{string(variables('Agreement Url'))}

Update Agreement Url on Document Approval​

  • Connector: Dataverse
  • Action Type: Update a row
    • Table Name: Document Approvals
    • Row ID: @{outputs('Get_Document_Approval_by_ID')?['body/tt_documentapprovalid']}
    • Agreement Items: @{outputs('Compose_Agreement_Url_String')}

List Document Location for Document Approval 2​

  • Connector: Dataverse
  • Action Type: List Rows
    • Table Name: Document Approvals
    • Fetxh XML Query: 
      <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="sharepointdocumentlocation">
          <attribute nadocument loccontme="name" />
          <attribute name="regardingobjectid" />
          <attribute name="parentsiteorlocation" />
          <attribute name="relativeurl" />
          <attribute name="absoluteurl" />
          <order attribute="name" descending="false" />
          <filter type="and">
          <condition attribute="locationtype" operator="eq" value="0" />
          <condition attribute="servicetype" operator="eq" value="0" />
          <condition attribute="regardingobjectid" operator="eq" uitype="tt_documentapproval" value="@{outputs('Get_Document_Approval_by_ID')?['body/tt_documentapprovalid']}" />
          </filter>
      </entity>
      </fetch>

Get Parent Document Location 2​

  • Connector: Dataverse
  • Action Type: Get a row by ID
    • Table Name: Document Approvals
    • Row ID: @{first(outputs('List_Document_Location_for_Document_Approval_2')?['body/value'])['_parentsiteorlocation_value']}

Set Folder Path 2​

  • Action Type: Set Variable
    • Name: Folder Path
    • Value: @{outputs('Get_Parent_Document_Location_2')?['body/relativeurl']}/@{first(outputs('List_Document_Location_for_Document_Approval_2')?['body/value'])['relativeurl']}

Upload Signed PDF​

  • Connector: SharePoint
  • Action Type: Create File
    • Site Address: @{parameters('CRMPortal SharePoint URL (tt_CRMPortalSharePointURL)')}
    • Folder Path: @{variables('Folder Path')}
    • File Name: @{variables('File Name')} - Signed.pdf
    • File Content: @{body('Get_a_PDF_of_a_signed_agreement')}

Parse JSON 2​

  • Action Type: Parse JSON
    • Content: @{outputs('Compose_Agreement_Url_String')}
    • Schema: 
      {
          "type": "array",
          "items": {
              "type": "object",
              "properties": {
                  "DocumentName": {
                      "type": "string"
                  },
                  "DocumentURL": {
                      "type": "string"
                  },
                  "Status": {
                      "type": "string"
                  },
                  "AgreementID": {
                      "type": "string"
                  }
              },
              "required": [
                  "DocumentName",
                  "DocumentURL",
                  "Status",
                  "AgreementID"
              ]
          }
      }

Apply to each Agreement Url 2​

  • Action Type: Apply to Each
    • Value: @{body('Parse_JSON_2')}

Agreement is Signed 2​

  • Action Type: Condition
    • Condition Type: AND
      • Condition: @{items('Apply_to_each_Agreement_Url_2')['Status']} is not equal to Signed
Set AllFilesSigned​
  • Action Type: Set Variable
    • Name: AllFilesSigned
    • Value: @{false}

Is Contact​

  • Action Type: Condition
    • Condition Type: AND
      • Condition: @{outputs('Get_Document_Approval_by_ID')?['body/_tt_contactid_value']} is equal to @{outputs('Get_Document_Approval_by_ID')?['body/_tt_documentsigner_value']}

Service Line​

  • Action Type: Compose
    • Value: 
      @{if(
          equals(
              body('Get_Document_Approval_by_ID')?['tt_serviceline@OData.Community.Display.V1.FormattedValue'],
              'Business Tax'
          ),
          'Corporation Tax',
          if(
              equals(
                  body('Get_Document_Approval_by_ID')?['tt_serviceline@OData.Community.Display.V1.FormattedValue'],
                  'General'
              ),
              'General Documents',
              body('Get_Document_Approval_by_ID')?['tt_serviceline@OData.Community.Display.V1.FormattedValue']
          )
      )}

Contact​

  • Action Type: Compose
    • Value: @{outputs('Get_Document_Approval_by_ID')?['body/_tt_contactid_value']}

List Service Line Document Location​

  • Connector: Dataverse
  • Action Type: List Rows
    • Table Name: Document Approvals
    • Fetxh XML Query: 
      <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
          <entity name="sharepointdocumentlocation">
              <attribute name="name" />
              <attribute name="regardingobjectid" />
              <attribute name="parentsiteorlocation" />
              <attribute name="relativeurl" />
              <attribute name="absoluteurl" />
              <order attribute="name" descending="false" />
              <filter type="and">
              <condition attribute="locationtype" operator="eq" value="0" />
              <condition attribute="servicetype" operator="eq" value="0" />
              </filter>
              <link-entity name="tt_contactdocuments" from="tt_contactdocumentsid" to="regardingobjectid" link-type="inner" alias="ac">
              <filter type="and">
                  <condition attribute="tt_contact" operator="eq" uitype="contact" value="@{outputs('Contact')}" />
                  <condition attribute="tt_name" operator="eq" value="@{outputs('Service_Line')}" />
              </filter>
              </link-entity>
          </entity>
      </fetch>

Get a Service Line Document Location by ID​

  • Connector: Dataverse
  • Action Type: Get a Row by ID
    • Table Name: Document Locations
    • Row ID: first(outputs('List_Service_Line_Document_Location')?['body/value'])?['sharepointdocumentlocationid']

Parse SharePoint Details​

  • Action Type: Parse JSON
    • Content: @{outputs('Compose_Agreement_Url_Array')}
    • Schema: 
      {
          "type": "array",
          "items": {
              "type": "object",
              "properties": {
                  "DocumentName": {
                      "type": "string"
                  },
                  "DocumentURL": {
                      "type": "string"
                  },
                  "Status": {
                      "type": "string"
                  },
                  "AgreementID": {
                      "type": "string"
                  }
              },
              "required": [
                  "DocumentName",
                  "DocumentURL",
                  "Status",
                  "AgreementID"
              ]
          }
      }

Relative Path​

  • Action Type: Compose
    • Value:
    if(
        startsWith(body('Parse_SharePoint_Details')?['Relative Path'], '/'),
        substring(body('Parse_SharePoint_Details')?['Relative Path'], 1),
        body('Parse_SharePoint_Details')?['Relative Path']
    )

Upload Signed PDF to Service Line folder​

  • Connector: SharePoint
  • Action Type: Create File
    • Site Address: @{body('Parse_SharePoint_Details')?['SharePoint Site']}
    • Folder Path: @{body('Parse_SharePoint_Details')?['Document Library']}/@{outputs('Relative_Path')}
    • File Name: @{variables('File Name')} - Signed.pdf
    • File Content: @{body('Get_a_PDF_of_a_signed_agreement')}

Condition​

  • Action Type: Condition
    • Condition Type: AND
      • Condition: @{variables('AllFilesSigned')} is equal to @{true}

Set Status Reason​

  • Action Type: Condition
    • Condition Type: AND
      • Condition: @{outputs('Get_Document_Approval_by_ID')?['body/_tt_contactid_value']} is equal to @{outputs('Get_Document_Approval_by_ID')?['body/_tt_documentsigner_value']}
Set Status Reason to Approved​
  • Action Type: Set Variable
    • Name: Status Reason
    • Value: @{int('206340000')}
Set Status to Active​
  • Action Type: Set Variable
    • Name: Status
    • Value: @{int('0')}

Update Agreement Url on Document Approval​

  • Connector: Dataverse
  • Action Type: Update a row
    • Table Name: Document Approvals
    • Row ID: @{outputs('Get_Document_Approval_by_ID')?['body/tt_documentapprovalid']}
    • Name: @{outputs('Get_Document_Approval_by_ID')?['body/tt_name']} - Signed
    • Approval Date: @{utcNow()}
    • Status: @{variables('Status')}
    • Status Reason: @{variables('Status Reason')}